package com.rbn.stockmobiledevices.admin.internal;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.MappedByteBuffer;
import java.nio.channels.FileChannel;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.DatabaseSequenceFilter;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.FilteredDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.dataset.xml.FlatXmlWriter;
import org.dbunit.ext.mysql.MySqlDataTypeFactory;
import org.dbunit.operation.DatabaseOperation;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.Transactional;

import com.rbn.stockmobiledevices.admin.AdminManager;

public class AdminManagerImpl implements AdminManager {

	private static final Logger logger = Logger.getLogger(AdminManagerImpl.class);
	private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	private static final String MYSQL_CATALOG_DBNAME = "TABLE_CAT";
	
	private Resource ddl;
	private SessionFactory sessionFactory;
	DriverManagerDataSource dataSource;
	
	
	public AdminManagerImpl(SessionFactory sessionFactory, DriverManagerDataSource dataSource) {
		this.sessionFactory = sessionFactory;
		this.dataSource = dataSource;
	}
	
	public Map<String, String> getDbConfiguration()  {
		logger.info("IN > getDbConfiguration");
		Map<String, String> retorno = new HashMap<String, String>();
		String url = null;
		String database = null;
		try{
			url = dataSource.getUrl();
			int index = url.lastIndexOf("/");
			database = url.substring(index+1);
			url = url.substring(0, index);
			
			retorno.put(AdminManager.DB_INFORMATION_JDBC_USUARIO, dataSource.getUsername());
			retorno.put(AdminManager.DB_INFORMATION_JDBC_PASSWORD, dataSource.getPassword());
			retorno.put(AdminManager.DB_INFORMATION_JDBC_URL, url);
			retorno.put(AdminManager.DB_INFORMATION_JDBC_DBNAME, database);
		}catch(Exception e){
			logger.error("Error al intentar obtener la configuracion de base de datos", e);
			return null;
		}finally{
			logger.info("OUT < getDbConfiguration");
		}
		return retorno;
	}
	
	public boolean dbIsCreated(String jdbc_url, String jdbc_user,
			String jdbc_password, String dbName) throws SQLException,
			ClassNotFoundException {
		logger.info("IN > dbIsCreated");
		try{
		List<String> list=new ArrayList<String>();
		Class.forName(MYSQL_DRIVER);
		Connection con = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_password);
		DatabaseMetaData meta = con.getMetaData();
		ResultSet rs = meta.getCatalogs();
		while (rs.next()) {
			String listofDatabases=rs.getString(MYSQL_CATALOG_DBNAME);
		    list.add(listofDatabases);
		}
		rs.close();
		
		if(list.contains(dbName)){
			return true;
		}else{
			return false;
		}
		}finally{
			logger.info("OUT < dbIsCreated");
		}
	}
	
	public Map<String, Object> getDbInformation(String jdbc_url, String jdbc_user, String jdbc_password, String dbName) throws ClassNotFoundException, SQLException{
		
		logger.info("IN > getDbInformation");
		
		Map<String, Object> retorno = new HashMap<String, Object>();
		List<String> list=new ArrayList<String>();
		
		try{
			Class.forName(MYSQL_DRIVER);
			Connection con = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_password);
			DatabaseMetaData meta = con.getMetaData();
			ResultSet rs = meta.getCatalogs();
			while (rs.next()) {
				String listofDatabases=rs.getString(MYSQL_CATALOG_DBNAME);
			    list.add(listofDatabases);
			}
			rs.close();
			retorno.put(AdminManager.DB_INFORMATION_DBS, list);
			con.close();
			
			if(list.contains(dbName)){
				Connection conSelectedDb = DriverManager.getConnection(jdbc_url+"/"+dbName, jdbc_user, jdbc_password);
				List<String> tablas = readTables(conSelectedDb);
				retorno.put(AdminManager.DB_INFORMATION_TABLAS, tablas);
				conSelectedDb.close();
			}
			
			return retorno;
		}finally{
			logger.info("OUT < getDbInformation");
		}
	}
	
	private List<String> readTables(Connection connection) throws SQLException {
		logger.info("IN > readTables");

		List<String> retorno = new ArrayList<String>();
		DatabaseMetaData metadata = null;
		
		try{
			metadata = connection.getMetaData();
			String[] names = { "TABLE", "VIEW" };
			ResultSet tableNames = metadata.getTables(null, "%", "%", names);
			while (tableNames.next()) {
				retorno.add(tableNames.getString("TABLE_NAME"));
				// readTableColumns(metadata, table); tables.addElement(table);
			}
			return retorno;
		}finally{
			logger.info("OUT < readTables");
		}
	}
	
	/*private void readTableColumns(DatabaseMetaData meta, String tableName)
			throws SQLException {
		logger.info("IN > readTableColumns");
		try {
			ResultSet columns = meta.getColumns(null, "%", tableName, "%");
			while (columns.next()) {
				String columnName = columns.getString("COLUMN_NAME");
				String datatype = columns.getString("TYPE_NAME");
				int datasize = columns.getInt("COLUMN_SIZE");
				int digits = columns.getInt("DECIMAL_DIGITS");
				int nullable = columns.getInt("NULLABLE");
				boolean isNull = (nullable == 1);
				// DbColumn newColumn = new DbColumn(columnName, datatype,
				// datasize, digits, isNull);
				// table.addColumn(newColumn);
			}
		} finally {
			logger.info("OUT < readTableColumns");
		}
	}*/

	@Transactional
	public void createDB() {
		logger.info("IN > createDB");
		try{
			Map<String, String> conf = getDbConfiguration();
			String url = conf.get(AdminManager.DB_INFORMATION_JDBC_URL);
			String usuario = conf.get(AdminManager.DB_INFORMATION_JDBC_USUARIO);
			String password = conf.get(AdminManager.DB_INFORMATION_JDBC_PASSWORD);
			String dbname = conf.get(AdminManager.DB_INFORMATION_JDBC_DBNAME);
			
			boolean isOk = false;
	
			File f = null;
			String s = null;
			
			try {
				f = ddl.getFile();
				s = readFile(f);
			} catch (IOException e1) {
				// retornar error
				e1.printStackTrace();
			}
			try {
				boolean isCreated = dbIsCreated(url, usuario, password, dbname);
				if(isCreated){
					deleteSchema(url, usuario, password, dbname);
				}
				isOk = createSchema(url, usuario, password, dbname);
				if(isOk){
					isOk = executeDDL(s, ";", url, usuario, password, dbname);
				}
			} catch (SQLException e) {
				// retornar error
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// retornar error
				e.printStackTrace();
			}
		}finally{
			logger.info("OUT < createDB");
		}
	}
	
	private static String readFile(File file) throws IOException {
		logger.info("IN > readFile");
		FileInputStream stream = new FileInputStream(file);
		try {
			FileChannel fc = stream.getChannel();
			MappedByteBuffer bb = fc.map(FileChannel.MapMode.READ_ONLY, 0, fc.size());
			/* Instead of using default, pass in a decoder. */
			return Charset.defaultCharset().decode(bb).toString();
		}
		finally {
			stream.close();
			logger.info("OUT < readFile");
		}
	}

	@Transactional
	private boolean executeDDL(String ddl, String delimiter, String url, String username, String password, String dbname) {
		logger.info("IN > executeDDL");
	    Connection con = null;
	    try {
	    	Class.forName(AdminManagerImpl.MYSQL_DRIVER);
	        con = DriverManager.getConnection(url+"/"+dbname, username, password);

	        // enable transaction
	        con.setAutoCommit(false);
	        Statement statement = con.createStatement();
	        
	        // for every DDL statement, execute it
	        for (String sql : ddl.split(delimiter)) {
	            if (sql.trim().length()>0) {
	                statement.executeUpdate(sql);
	            }
	        }
	        statement.close();
	        con.commit();
	        return true;
	    }
	    catch (Exception e) {
	        e.printStackTrace();
	        try {
	        	if(con!=null){
	        		con.rollback();
	        		// logger.log(rollback transaction);
	        	}
			} catch (SQLException ignored) {
				//ignored.printStackTrace();
			}
	        return false;
	    }
	    finally {
	        try {
	            con.close();
	        }
	        catch (Exception ignored) {
	        }
	        logger.info("OUT < executeDDL");
	    }
	}
	@Transactional
	private boolean createSchema(String url, String username, String password, String dbname) {
		logger.info("IN > createSchema");
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName(AdminManagerImpl.MYSQL_DRIVER);

			// logger
			// System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(url, username, password);

			// logger
			// System.out.println("Creating database...");
			stmt = conn.createStatement();

			String sql = "CREATE SCHEMA "+ dbname ;
			stmt.executeUpdate(sql);
			//logger
			//System.out.println("Database created successfully...");
			
			return true;
		} catch (SQLException se) {
			se.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException ignored) {
				//ignored.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException ignored) {
				//ignored.printStackTrace();
			}
			logger.info("OUT < createSchema");
		}
	}
	
	@Transactional
	private boolean deleteSchema(String url, String username, String password, String dbname) {
		
		logger.info("IN > deleteSchema");
		
		Connection conn = null;
		Statement stmt = null;
		
		try {
			Class.forName(AdminManagerImpl.MYSQL_DRIVER);

			// logger
			// System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(url, username, password);

			// logger
			// System.out.println("Creating database...");
			stmt = conn.createStatement();

			String sql = "drop schema "+ dbname ;
			stmt.executeUpdate(sql);
			//logger
			//System.out.println("Database created successfully...");
			
			return true;
		} catch (SQLException se) {
			se.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException ignored) {
				//ignored.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException ignored) {
				//ignored.printStackTrace();
			}
			logger.info("OUT < deleteSchema");
		}
	}
	
	public void exportDbData(OutputStream out) throws SQLException {

		logger.info("IN > exportDbData");
		
		try{
			Map<String, String> conf = getDbConfiguration();
			String url = conf.get(AdminManager.DB_INFORMATION_JDBC_URL);
			String usuario = conf.get(AdminManager.DB_INFORMATION_JDBC_USUARIO);
			String password = conf.get(AdminManager.DB_INFORMATION_JDBC_PASSWORD);
			String dbname = conf.get(AdminManager.DB_INFORMATION_JDBC_DBNAME);
			
			Connection conn = null;
	
			try {
				// Connect to the database
				DriverManager.registerDriver((Driver) Class.forName(AdminManagerImpl.MYSQL_DRIVER).newInstance());
				conn = DriverManager.getConnection(url+"/"+dbname, usuario, password);
				IDatabaseConnection connection = new DatabaseConnection(conn, dbname);
				DatabaseConfig dbConfig = connection.getConfig();
				dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
				
				DatabaseSequenceFilter filter = new DatabaseSequenceFilter(connection);
				IDataSet datasetAll = new FilteredDataSet(filter, connection.createDataSet());
				QueryDataSet partialDataSet = new QueryDataSet(connection);
	
				String[] listTableNames = filter.getTableNames(datasetAll);
				for (int i = 0; i < listTableNames.length; i++) {
					final String tableName = listTableNames[i];
					// Specify the SQL to run to retrieve the data
					partialDataSet.addTable(tableName);
				}
				// Specify the location of the flat file(XML)
				FlatXmlWriter datasetWriter = new FlatXmlWriter(out);
				// Export the data
				datasetWriter.write(partialDataSet);
			} catch (Exception exc) {
				exc.printStackTrace();
	
			} finally {
				conn.close();
			}
		}finally{
			logger.info("OUT < exportDbData");
		}
	}
	
	@Transactional
	public void importDbData(InputStream in) throws SQLException {
		logger.info("IN > importDbData");
		try{
			createDB();
			Map<String, String> conf = getDbConfiguration();
			String url = conf.get(AdminManager.DB_INFORMATION_JDBC_URL);
			String usuario = conf.get(AdminManager.DB_INFORMATION_JDBC_USUARIO);
			String password = conf.get(AdminManager.DB_INFORMATION_JDBC_PASSWORD);
			String dbname = conf.get(AdminManager.DB_INFORMATION_JDBC_DBNAME);
			
			Connection conn = null;
			try {
				// Connect to the database
				DriverManager.registerDriver((Driver) Class.forName(AdminManagerImpl.MYSQL_DRIVER).newInstance());
				conn = DriverManager.getConnection(url+"/"+dbname, usuario, password);
				IDatabaseConnection connection = new DatabaseConnection(conn);
				
				DatabaseConfig dbConfig = connection.getConfig();
				dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
				
				FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
				builder.setColumnSensing(true);
				IDataSet dataSet = builder.build(in);
				
				DatabaseOperation.INSERT.execute(connection, dataSet);
			} catch (Exception exc) {
				exc.printStackTrace();
			} finally {
				conn.close();
			}
		}finally{
			logger.info("OUT < importDbData");
		}
	}
	
	protected Session getCurrentSession() {
		return sessionFactory.getCurrentSession();
	}
	public Resource getDdl() { return ddl; }
	public void setDdl(Resource ddl) { this.ddl = ddl; }
}